/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pl.cezary.butler.walili.server.db.utils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.apache.commons.lang.StringEscapeUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.Assert;
import pl.cezary.butler.walili.server.db.tables.ITableDefinition;

/**
 *
 * @author czarek
 */
public class JdbcTemplate extends org.springframework.jdbc.core.JdbcTemplate {

    private static final Logger log = Logger.getLogger(JdbcTemplate.class.getName());
    public JdbcTemplate(DataSource dataSource) {
        super(dataSource);
    }
    
    public int insert(Class<? extends ITableDefinition> table , Object... params){    
        KeyHolder holder = new GeneratedKeyHolder();
        update(TableDefinitionUtils.getPreparedStatementCreator(table, params),holder);        
        return holder.getKey().intValue();
    }
    
    private static String convertToString(Object obj){
        if(obj == null){
            return null;
        }
        if(obj instanceof String){
            return "'"+StringEscapeUtils.escapeSql((String)obj)+"'";
        }      
        return obj.toString();
    }
    
    public int queryForIntIfEmpty(String sql, Object... params){
        return queryForIntIfEmpty(sql, 0, params);
    }
    
    public int queryForIntIfEmpty(String sql, int valueIfEmpty, Object... params){
        int wynik = valueIfEmpty;
        final List<Integer> res = queryForList(sql,Integer.class, params );
        if(res.size()>0)
            wynik = res.get(0);
        if(res.size()>1){
            logger.warn("More than 1 row found where expected 1");
        }
        return wynik;
    }

    public int insert(final String sql) throws DataAccessException {
        Assert.notNull(sql, "SQL must not be null");
        if (logger.isDebugEnabled()) {
            logger.debug("Executing SQL update [" + sql + "]");
        }
        class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider {

            @Override
            public Integer doInStatement(Statement stmt) throws SQLException {
                int rows = stmt.executeUpdate(sql);
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL update affected " + rows + " rows");
                }
                int addedId = 0;
                ResultSet rs = stmt.getGeneratedKeys();
                try {
                    if (rs.next()) {
                        addedId = rs.getInt(1);
                    }else{
                        log.severe("Nie udało się pobrać wygenerowanej wartości identyfikatora dla wpisu");
                    }
                } finally {
                    rs.close();
                }

                return addedId;
            }

            @Override
            public String getSql() {
                return sql;
            }
        }
        return execute(new UpdateStatementCallback());
    }

    protected int insert(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
            throws DataAccessException {

        logger.debug("Executing prepared SQL update");
        return execute(psc, new PreparedStatementCallback<Integer>() {

            @Override
            public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
                try {
                    if (pss != null) {
                        pss.setValues(ps);
                    }
                    int rows = ps.executeUpdate();
                    int addedId = 0;
                    ResultSet rs = ps.getGeneratedKeys();
                    try {
                        if (rs.next()) {
                            addedId = rs.getInt(1);
                        }
                    } finally {
                        rs.close();
                    }
                    if (logger.isDebugEnabled()) {
                        logger.debug("SQL inserted " + rows + " rows #" + addedId);
                    }



                    return rows;
                } finally {
                    if (pss instanceof ParameterDisposer) {
                        ((ParameterDisposer) pss).cleanupParameters();
                    }
                }
            }
        });
    }

    public int insert(PreparedStatementCreator psc) throws DataAccessException {
        return insert(psc, (PreparedStatementSetter) null);
    }

    public int insert(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
            throws DataAccessException {

        Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
        logger.debug("Executing SQL update and returning generated keys");

        return execute(psc, new PreparedStatementCallback<Integer>() {

            @Override
            public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
                int rows = ps.executeUpdate();
                List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
                generatedKeys.clear();
                ResultSet keys = ps.getGeneratedKeys();
                if (keys != null) {
                    try {
                        RowMapperResultSetExtractor<Map<String, Object>> rse =
                                new RowMapperResultSetExtractor<Map<String, Object>>(getColumnMapRowMapper(), 1);
                        generatedKeys.addAll(rse.extractData(keys));
                    } finally {
                        JdbcUtils.closeResultSet(keys);
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
                }
                return rows;
            }
        });
    }


    public int insert(String sql, Object[] args, int[] argTypes) throws DataAccessException {
        return insert(sql, newArgTypePreparedStatementSetter(args, argTypes));
    }

    public int insert(String sql, Object... args) throws DataAccessException {
        return insert(sql, newArgPreparedStatementSetter(args));
    }
    
    
}
